
version = '0.2.0'
author  = 'Mike Taylor'
license = """
Copyright (c) 2007 Mike Taylor
All rights reserved.

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

   http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
"""

"""
tf_store.py

Set of classes to interface to the database.  Currrently sqlite3

Requires:
    Python          2.5 is what I'm using - haven't tried it on any other
                    http://python.org

Contact Info:
    bear            aka Mike Taylor
                    bear42@gmail.com (email and jabber id)
"""


import sys, os
import datetime
import sqlite3


class Store():
    def __init__(self, storepath=''):
        self.storename = os.path.join(storepath, 'twitter.data')
        self.db        = sqlite3.connect(self.storename)

        self.db.isolation_level = None
        self.db.row_factory     = sqlite3.Row

        self.checkTables()

    def close(self):
        self.db.commit()

        self.db    = None
        self.cache = None

    def execute(self, sql, args=[]):
        n      = 0
        result = None

        while True:
            try:
                result = self.db.execute(sql, args)
                break
            except:
                print 'exception during execute - retrying', n

            n += 1
            if n > 5:
                raise sqlite3.OperationalError

        return result

    def reccount(self):
        return self.execute('select count(*) from posts').fetchone()[0]

    _posts_select = 'select post_id, post_timestamp, screen_name, name, location, imageurl, url, desc, post, created, updated, filtered from posts'
    _posts_insert = 'insert into posts(post_id, post_timestamp, screen_name, name, location, imageurl, url, desc, post, created, updated, filtered) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
    _posts_update = 'update posts set post_timestamp=?, screen_name=?, name=?, location=?, imageurl=?, url=?, desc=?, post=?, updated=?, filtered=? where post_id = ?'
    _posts_mark   = 'update posts set filtered="Y" where post_id = ?'

    def update(self, item):
        now          = datetime.datetime.now()
        item.updated = now

        if item.isNew:
            item.created = now

            self.execute(self._posts_insert, [item.post_id,
                                              item.post_timestamp,
                                              item.screen_name,
                                              item.name,
                                              item.location,
                                              item.imageurl,
                                              item.url,
                                              item.desc,
                                              item.post,
                                              item.created,
                                              item.updated,
                                              "N"])
            item.isNew = False
        else:
            self.execute(self._posts_update, [item.post_timestamp,
                                              item.screen_name,
                                              item.name,
                                              item.location,
                                              item.imageurl,
                                              item.url,
                                              item.desc,
                                              item.post,
                                              item.updated,
                                              item.filtered,
                                              item.post_id])

        self.db.commit()

    def count(self, post_id):
        try:
            result = int(self.execute('select count(*) from posts where post_id = ?', [post_id]).fetchone()[0])
        except:
            result = -1

        return result

    def findPost(self, post_id):
        item = Item()
        row  = self.execute('%s where post_id = ?' % self._posts_select, [post_id]).fetchone()

        item.load(row)

        return item

    def find(self, findValues):
        query  = '%s where ' % self._posts_select
        values = []
        result = []

        for key in findValues:
            query += ' %s = ?' % key
            values.append(findValues[key])

        if len(values) > 0:
            rows = self.execute(query, values).fetchall()

            for row in rows:
                item = Item()
                item.load(row)
                result.append(item)

        return result

    def findLast(self, jid, hours=None, date=None):
        query  = 'select * from posts, history where history.post_id = posts.post_id'
        result = []

        if hours is not None:
            query += " and datetime(history.published) >= datetime('now', '-%d hours')" % hours
            rows   = self.execute(query).fetchall()

            for row in rows:
                item = Item()
                item.load(row)
                result.append(item)
        elif date is not None:
            query += " and date(history.published) = date('%s')" % date
            rows   = self.execute(query).fetchall()

            for row in rows:
                item = Item()
                item.load(row)
                result.append(item)

        return result

    def getNewPosts(self):
        rows   = self.execute('%s where filtered = "N"' % self._posts_select).fetchall()
        result = []

        for row in rows:
            item = Item()
            item.load(row)
            result.append(item)

        return result

    def markFiltered(self, post_id):
        self.execute(self._posts_mark, [post_id])

    def postHistoryEntry(self, jid, item):
        sql = 'insert into history(jid, post_id, published) values(?, ?, ?)'
        self.execute(sql, [jid, item.post_id, item.created])

    def postStatsEntry(self, stats):
        sql = 'insert into stats(stat_date, reccount, users, commands, polled, published) values(?, ?, ?, ?, ?, ?)'
        self.execute(sql, [datetime.datetime.now(),
                           stats['records'],
                           stats['users'],
                           stats['commands'],
                           stats['polled'],
                           stats['published'],
                          ])

    def dump(self):
        print 'posts: %d' % self.db.execute('select count(*) from posts').fetchone()[0]

        for row in self.execute(self._posts_select).fetchall():
            item = Item()
            item.load(row)
            print item.dump().encode('ascii', 'replace')

    def checkTables(self):
        """
        Check for required tables, create if needed
        Note: currently assumes sqlite3
        """
        _tables = [ '''CREATE TABLE IF NOT EXISTS posts (
                          post_id        INTEGER PRIMARY KEY,
                          post_timestamp TEXT,
                          screen_name    TEXT,
                          name           TEXT,
                          location       TEXT,
                          imageurl       TEXT,
                          url            TEXT,
                          desc           TEXT,
                          post           TEXT,
                          created        TIMESTAMP,
                          updated        TIMESTAMP,
                          filtered       TEXT )''',
                    '''CREATE TABLE IF NOT EXISTS stats (
                          stat_date      TIMESTAMP,
                          reccount       INTEGER,
                          users          INTEGER,
                          commands       INTEGER,
                          polled         INTEGER,
                          published      INTEGER )''',
                    '''CREATE TABLE IF NOT EXISTS history (
                          jid            TEXT,
                          post_id        INTEGER,
                          published      TIMESTAMP )''',
                  ]

        count = self.execute('select count(*) from sqlite_master').fetchone()[0]

        if count != len(_tables):
            print 'Creating tables in %s' % self.storename

            for table in _tables:
                self.execute(table)

            self.db.commit()


class Item():
    def __init__(self):
        self.isNew          = True
        self.post_id        = 0
        self.post_timestamp = ''
        self.screen_name    = ''
        self.name           = ''
        self.location       = ''
        self.imageurl       = ''
        self.url            = ''
        self.desc           = ''
        self.post           = ''
        self.filtered       = 'N'
        self.created        = datetime.datetime.now()
        self.updated        = datetime.datetime.now()

    def load(self, row):
        if row is None:
            self.isNew = True
        else:
            self.isNew          = False
            self.post_id        = row["post_id"]
            self.post_timestamp = row["post_timestamp"]
            self.screen_name    = row["screen_name"]
            self.name           = row["name"]
            self.location       = row["location"]
            self.imageurl       = row["imageurl"]
            self.url            = row["url"]
            self.desc           = row["desc"]
            self.post           = row["post"]
            self.filtered       = row["filtered"]
            self.created        = datetime.datetime.strptime(row["created"][:19], '%Y-%m-%d %H:%M:%S')
            self.updated        = datetime.datetime.strptime(row["updated"][:19], '%Y-%m-%d %H:%M:%S')

    def dump(self):
        return '''%d: %s %s %s %s\n%s %s %s %s\n%s''' % (self.post_id, self.filtered, self.post_timestamp, self.created, self.updated, self.screen_name, self.name, self.location, self.desc, self.post)

